/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package admintool.imp03_data;

import admintool.AdminToolView;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author Administrator
 */
public class IMP03_Vacancy {

    private static ArrayList<IMP03_Vacancy> availableVacancies = new ArrayList<IMP03_Vacancy>();
    
    /**
     * 
     * @param vacancyID
     * @return
     */
    public static IMP03_Vacancy getSpecificVacancyFromDB(int vacancyID){
        ResultSet rs = null;
        PreparedStatement ps = null;
        IMP03_Vacancy result = null;
        
        try {
            ps = AdminToolView.getDBConnection().prepareStatement("SELECT V.VacancyID, V.Title, V.Description, C.Name, V.PLZ, V.StartDate, Q.Name, V.Budget, V.AnualSalary, V.FullTimePartTime, V.ExperienceInYears, Q.QualificationID FROM (vacancy V LEFT JOIN qualification Q ON V.QualificationID = Q.QualificationID) LEFT JOIN company C ON V.CompanyID = C.CompanyID WHERE VacancyID = ?");
            ps.setInt(1, vacancyID);
            rs = ps.executeQuery();
            while(rs.next()){
                result = new IMP03_Vacancy(rs.getInt(1), 
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getDate(6),
                        rs.getString(7),
                        rs.getInt(8),
                        rs.getInt(9),
                        rs.getBoolean(10),
                        rs.getInt(11),
                        rs.getInt(12));
            }
        } catch (Exception e) {
            e.printStackTrace();
       } finally {
            try {
                if(rs != null){
                    rs.close();
                }
                if(ps != null){
                    ps.close();
                }
            } catch (Exception e) {
            }
        }
         
        return result;
    }

    /**
     * 
     * @param reload
     * @return
     */
    public static ArrayList<IMP03_Vacancy> getAllAvailableVacanciesFromDB(boolean reload){
        ResultSet rs = null;
        PreparedStatement ps = null;
        
        try {
            if(reload == true || availableVacancies.isEmpty()) {
                availableVacancies.clear();
                ps = AdminToolView.getDBConnection().prepareStatement("SELECT V.VacancyID, V.Title, V.Description, C.Name, V.PLZ, V.StartDate, Q.Name, V.Budget, V.AnualSalary, V.FullTimePartTime, V.ExperienceInYears, V.QualificationID FROM (vacancy V LEFT JOIN qualification Q ON V.QualificationID = Q.QualificationID) LEFT JOIN company C ON V.CompanyID = C.CompanyID");
                rs = ps.executeQuery();
                while(rs.next()){
                    availableVacancies.add(new IMP03_Vacancy(rs.getInt(1), 
                            rs.getString(2),
                            rs.getString(3),
                            rs.getString(4),
                            rs.getString(5),
                            rs.getDate(6),
                            rs.getString(7),
                            rs.getInt(8),
                            rs.getInt(9),
                            rs.getBoolean(10),
                            rs.getInt(11),
                            rs.getInt(12)));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
       } finally {
            try {
                if(rs != null){
                    rs.close();
                }
                if(ps != null){
                    ps.close();
                }
            } catch (Exception e) {
            }
        }
         
        return availableVacancies;
    }
    
    /**
     * 
     * @return
     */
    public static IMP03_Vacancy getEmptyVacancyObject(){
        return new IMP03_Vacancy(-1, AdminToolView.EMPTY, AdminToolView.EMPTY, AdminToolView.EMPTY, AdminToolView.EMPTY, null, AdminToolView.EMPTY, -1, -1, false, -1, -1);
    }
    
    private int vacancyID = -1;
    private String vacancyTitle = "";
    private String vacancyDesc = "";
    private String vacancyCompanyName = "";
    private String vacancyPLZ = "";
    private Date vacancyStartDate = null;
    private String vacancyQualification = "";
    private int vacancyQualificationID = 0;
    private int vacancayExperienceInYears = 0;
    private int vacancyAnualSalary = 0;
    private int vacancyBudget = 0;
    private boolean vacancyFullTimePartTime = false;
    
    private ArrayList<IMP03_Platform> vacancyPlatforms = new ArrayList<IMP03_Platform>();
    private ArrayList<IMP03_Skill> vacancySkills = new ArrayList<IMP03_Skill>();

    private IMP03_Vacancy(int vacancyID, String vacancyTitle, String vacancyDesc, String vacancyCompanyName,
            String vacancyPLZ, Date vacancyStartDate, String vacancyQualification, 
            int vacancyBudget, int vacancyAnualSalary, 
            boolean vacancyFullTimePartTime, int vacancayExperienceInYears, int vacancyQualificationID) {
        this.vacancyID = vacancyID;
        this.vacancyTitle = vacancyTitle;
        this.vacancyDesc = vacancyDesc;
        this.vacancyCompanyName = vacancyCompanyName;
        this.vacancyPLZ = vacancyPLZ;
        this.vacancyStartDate = vacancyStartDate;
        this.vacancyQualification = vacancyQualification;
        this.vacancyBudget = vacancyBudget;
        this.vacancyAnualSalary = vacancyAnualSalary;
        this.vacancyFullTimePartTime = vacancyFullTimePartTime;        
        this.vacancayExperienceInYears = vacancayExperienceInYears;
        this.vacancyQualificationID = vacancyQualificationID;
    }

    /**
     * 
     * @return
     */
    public int getVacancyID() {
        return vacancyID;
    }

    /**
     * 
     * @return
     */
    public int getQualificationID() {
        return vacancyQualificationID;
    }

    /**
     * 
     * @return
     */
    public String getVacancyDesc() {
        return vacancyDesc;
    }

    /**
     * 
     * @return
     */
    public String getVacancyCompanyName() {
        return vacancyCompanyName;
    }

    /**
     * 
     * @return
     */
    public String getVacancyTitle() {
        return vacancyTitle;
    }

    /**
     * 
     * @return
     */
    public int getVacancyExperienceInYears() {
        return vacancayExperienceInYears;
    }

    /**
     * 
     * @return
     */
    public int getVacancyAnualSalary() {
        return vacancyAnualSalary;
    }

    /**
     * 
     * @return
     */
    public int getVacancyBudget() {
        return vacancyBudget;
    }

    /**
     * 
     * @return
     */
    public boolean isVacancyFullTimePartTime() {
        return vacancyFullTimePartTime;
    }

    /**
     * 
     * @return
     */
    public String getVacancyPLZ() {
        return vacancyPLZ;
    }

    /**
     * 
     * @return
     */
    public String getVacancyQualification() {
        return vacancyQualification;
    }

    /**
     * 
     * @return
     */
    public Date getVacancyStartDate() {
        return vacancyStartDate;
    }

    @Override
    public String toString() {
        return getVacancyTitle();
    }
    
    /**
     * 
     * @return
     */
    public ArrayList<IMP03_Platform> getVacancyPlatforms(){
        initPlaformVacancies();
        return vacancyPlatforms;    
    }    
    
    /**
     * 
     * @return
     */
    public ArrayList<IMP03_Skill> getVacancySkills(){
        initVacancySkills();
        return vacancySkills;    
    }  

    
    /**
     * 
     * @param skillID
     * @return
     */
    public int getSkillLevelForVacancy(int skillID){
        PreparedStatement ps = null;
        ResultSet rs = null;
        int skillLevel = -1;
        
        try {
            ps = AdminToolView.getDBConnection().prepareStatement("SELECT SkillLevel FROM vacancy_skills WHERE SkillID = ? AND VacancyID = ?");
            ps.setInt(1, skillID);
            ps.setInt(2, getVacancyID());
            rs = ps.executeQuery();
            while(rs.next()){
                skillLevel = rs.getInt(1);
                break;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            try {
                if(rs != null){
                    rs.close();
                }
                if(ps != null){
                    ps.close();
                }
            } catch (Exception e) {
            }
        }
        return skillLevel;
    }
    
    private void initPlaformVacancies() {
        PreparedStatement ps = null;
        ResultSet rs = null;
        vacancyPlatforms.clear();
        
        try {
            ps = AdminToolView.getDBConnection().prepareStatement("SELECT PlatformID FROM vacancy_platforms WHERE vacancyID = ?");
            ps.setInt(1, getVacancyID());
            rs = ps.executeQuery();
            ArrayList<Integer> platformIDs = new ArrayList<Integer>();
            while(rs.next()){
                platformIDs.add(rs.getInt(1));
            }
            
            for(IMP03_Platform p : IMP03_Platform.getAllAvailablePlatformsFromDB(false)){
                if(platformIDs.contains(p.getPlatformID())){
                    vacancyPlatforms.add(p);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            try {
                if(rs != null){
                    rs.close();
                }
                if(ps != null){
                    ps.close();
                }
            } catch (Exception e) {
            }
        }
    }
    
    private void initVacancySkills() {
        PreparedStatement ps = null;
        ResultSet rs = null;
        vacancySkills.clear();
        
        try {
            ps = AdminToolView.getDBConnection().prepareStatement("SELECT SkillID FROM vacancy_skills WHERE vacancyID = ?");
            ps.setInt(1, getVacancyID());
            rs = ps.executeQuery();
            ArrayList<Integer> skillIds = new ArrayList<Integer>();
            while(rs.next()){
                skillIds.add(rs.getInt(1));
            }
            
            for(IMP03_Skill s : IMP03_Skill.getAllAvailableSkillsFromDB(false)){
                if(skillIds.contains(s.getSkillID())){
                    vacancySkills.add(s);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            try {
                if(rs != null){
                    rs.close();
                }
                if(ps != null){
                    ps.close();
                }
            } catch (Exception e) {
            }
        }
    }    
}
